package com.ecsolutions.service;

import com.ecsolutions.dao.repaymentScheModify_DAO;
import com.ecsolutions.entity.repaymentScheModify_entity;
import com.ecsolutions.entity.repaymentSchedule_entity;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by ecs on 2017/9/4.
 */
@Service("repaymentScheModify_Service")
public class repaymentScheModify_ServiceImpl implements repaymentScheModify_Service{
    private repaymentScheModify_DAO repaymentschemodify_dao;

    public repaymentScheModify_ServiceImpl(repaymentScheModify_DAO repaymentschemodify_dao) {
        this.repaymentschemodify_dao = repaymentschemodify_dao;
    }

    @Override
    public int countResultList(String refno){
        int result = repaymentschemodify_dao.countResultList(refno);
        return result;
    }

    @Override
    public List<repaymentScheModify_entity> getResultList(String refno){
        List<repaymentScheModify_entity> result = repaymentschemodify_dao.getResultList(refno);
        return result;
    }

    @Override
    public repaymentScheModify_entity getLoanInfo(String refno){
        repaymentScheModify_entity result = repaymentschemodify_dao.getLoanDetails(refno);
        return result;
    }

    @Override
    public List<repaymentSchedule_entity> getRepaymentScheduleList(String refno){
        List<repaymentSchedule_entity> result = repaymentschemodify_dao.getRepaymentSchedule(refno);
        return result;
    }

    @Override
    public repaymentScheModify_entity getRepaymentSchedulePage(String refno) {
        return repaymentschemodify_dao.getRepaymentSchedulePageByRefno(refno);
    }

    @Override
    public String getIntrate(String refno){
        String result = repaymentschemodify_dao.queryForIntrate(refno);
        return result;
    }

    @Override
    public String getStrdate(String refno){
        String result = repaymentschemodify_dao.queryForStrdate(refno);
        return result;
    }

    @Override
    public void saveToAdsinmf4(repaymentSchedule_entity repaymentschedule_entity){
        repaymentschemodify_dao.insertToAdsinmf4(repaymentschedule_entity);
    }

    @Override
    public void saveToHkbppmas(repaymentScheModify_entity repaymentschemodify_entity){
        repaymentschemodify_dao.insertToHkbppmas(repaymentschemodify_entity);
    }

    @Override
    public void saveToHkbpptmp(repaymentScheModify_entity repaymentschemodify_entity,repaymentSchedule_entity repaymentschedule_entity){
        repaymentschemodify_dao.insertToHkbpptmp(repaymentschemodify_entity,repaymentschedule_entity);
    }

    @Override
    public void saveToHkbindtl(repaymentScheModify_entity repaymentschemodify_entity,repaymentSchedule_entity repaymentschedule_entity){
        repaymentschemodify_dao.insertToHkbindtl(repaymentschemodify_entity,repaymentschedule_entity);
    }

    @Override
    public String exportExcel(List<repaymentSchedule_entity> repaymentScheduleList,repaymentScheModify_entity repaymentschemodify_entity){
        //总还利息、总还款额、总还本金
        repaymentSchedule_entity SumInfo = repaymentschemodify_dao.getSumInfo(repaymentschemodify_entity.getRefno());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        HSSFWorkbook wb = new HSSFWorkbook();//1.创建一个workbook，对应一个Excel文件
        HSSFSheet sheet = wb.createSheet("还款计划表");// 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        for (int i = 0;i < 7;i++){
            sheet.setColumnWidth(i,8*512);
        }
        //设置样式1
        CellStyle styleOne = wb.createCellStyle(); //创建样式对象1
        styleOne.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleOne.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置字体样式1
        HSSFFont font1 = wb.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 11);//设置字体大小
        font1.setBold(true);//粗体显示
        styleOne.setFont(font1);
        //设置样式2
        CellStyle styleTwo = wb.createCellStyle(); //创建样式对象2
        styleTwo.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleTwo.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置字体样式2
        HSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 11);//设置字体大小
        styleTwo.setFont(font2);
        //设置样式3
        CellStyle styleThree = wb.createCellStyle(); //创建样式对象3
        //设置字体样式3
        HSSFFont font3 = wb.createFont();
        font3.setFontName("宋体");
        font3.setFontHeightInPoints((short) 11);//设置字体大小
        styleThree.setFont(font3);
        //设置样式4
        CellStyle styleFour = wb.createCellStyle(); //创建样式对象4
        styleFour.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleFour.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框粗细
        styleFour.setBorderTop(BorderStyle.THIN);//上边框细
        styleFour.setBorderBottom(BorderStyle.THIN);//下边框细
        styleFour.setBorderLeft(BorderStyle.THIN);//左边框细
        styleFour.setBorderRight(BorderStyle.THIN);//右边框细
        //设置边框颜色黑色
        styleFour.setTopBorderColor(IndexedColors.BLACK.index);//上边框黑色
        styleFour.setBottomBorderColor(IndexedColors.BLACK.index);//下边框黑色
        styleFour.setLeftBorderColor(IndexedColors.BLACK.index);//左边框黑色
        styleFour.setRightBorderColor(IndexedColors.BLACK.index);//右边框黑色
        //设置字体样式4
        HSSFFont font4 = wb.createFont();
        font4.setFontName("宋体");
        font4.setFontHeightInPoints((short) 11);//设置字体大小
        styleFour.setFont(font4);
        //设置样式5
        CellStyle styleFive = wb.createCellStyle(); //创建样式对象5
        //设置边框粗细
        styleFive.setBorderTop(BorderStyle.THIN);//上边框细
        styleFive.setBorderBottom(BorderStyle.THIN);//下边框细
        styleFive.setBorderLeft(BorderStyle.THIN);//左边框细
        styleFive.setBorderRight(BorderStyle.THIN);//右边框细
        //设置边框颜色黑色
        styleFive.setTopBorderColor(IndexedColors.BLACK.index);//上边框黑色
        styleFive.setBottomBorderColor(IndexedColors.BLACK.index);//下边框黑色
        styleFive.setLeftBorderColor(IndexedColors.BLACK.index);//左边框黑色
        styleFive.setRightBorderColor(IndexedColors.BLACK.index);//右边框黑色
        //设置字体样式5
        HSSFFont font5 = wb.createFont();
        font5.setFontName("宋体");
        font5.setFontHeightInPoints((short) 11);//设置字体大小
        styleFive.setFont(font5);
        //设置样式6
        CellStyle styleSix = wb.createCellStyle(); //创建样式对象6
        styleSix.setAlignment(HorizontalAlignment.RIGHT);//水平右对齐
        styleSix.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框粗细
        styleSix.setBorderTop(BorderStyle.THIN);//上边框细
        styleSix.setBorderBottom(BorderStyle.THIN);//下边框细
        styleSix.setBorderLeft(BorderStyle.THIN);//左边框细
        styleSix.setBorderRight(BorderStyle.THIN);//右边框细
        //设置边框颜色黑色
        styleSix.setTopBorderColor(IndexedColors.BLACK.index);//上边框黑色
        styleSix.setBottomBorderColor(IndexedColors.BLACK.index);//下边框黑色
        styleSix.setLeftBorderColor(IndexedColors.BLACK.index);//左边框黑色
        styleSix.setRightBorderColor(IndexedColors.BLACK.index);//右边框黑色
        //设置字体样式6
        HSSFFont font6 = wb.createFont();
        font6.setFontName("宋体");
        font6.setFontHeightInPoints((short) 11);//设置字体大小
        styleSix.setFont(font6);
        //设置样式7
        CellStyle styleSeven = wb.createCellStyle(); //创建样式对象7
        styleSeven.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleSeven.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框粗细
        styleSeven.setBorderTop(BorderStyle.THIN);//上边框细
        styleSeven.setBorderBottom(BorderStyle.THIN);//下边框细
        styleSeven.setBorderLeft(BorderStyle.THIN);//左边框细
        styleSeven.setBorderRight(BorderStyle.THIN);//右边框细
        //设置边框颜色黑色
        styleSeven.setTopBorderColor(IndexedColors.BLACK.index);//上边框黑色
        styleSeven.setBottomBorderColor(IndexedColors.BLACK.index);//下边框黑色
        styleSeven.setLeftBorderColor(IndexedColors.BLACK.index);//左边框黑色
        styleSeven.setRightBorderColor(IndexedColors.BLACK.index);//右边框黑色
        //设置字体样式7
        HSSFFont font7 = wb.createFont();
        font7.setFontName("宋体");
        font7.setFontHeightInPoints((short) 11);//设置字体大小
        font7.setBold(true);//粗体显示
        font7.setUnderline(Font.U_SINGLE);
        styleSeven.setFont(font7);
        //设置样式8
        CellStyle styleEight = wb.createCellStyle(); //创建样式对象8
        styleEight.setAlignment(HorizontalAlignment.RIGHT);//水平右对齐
        styleEight.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框粗细
        styleEight.setBorderTop(BorderStyle.THIN);//上边框细
        styleEight.setBorderBottom(BorderStyle.THIN);//下边框细
        styleEight.setBorderLeft(BorderStyle.THIN);//左边框细
        styleEight.setBorderRight(BorderStyle.THIN);//右边框细
        //设置边框颜色黑色
        styleEight.setTopBorderColor(IndexedColors.BLACK.index);//上边框黑色
        styleEight.setBottomBorderColor(IndexedColors.BLACK.index);//下边框黑色
        styleEight.setLeftBorderColor(IndexedColors.BLACK.index);//左边框黑色
        styleEight.setRightBorderColor(IndexedColors.BLACK.index);//右边框黑色
        //设置字体样式8
        HSSFFont font8 = wb.createFont();
        font8.setFontName("宋体");
        font8.setFontHeightInPoints((short) 11);//设置字体大小
        font8.setBold(true);//粗体显示
        font8.setUnderline(Font.U_SINGLE);
        styleEight.setFont(font8);
        //创建单元格在第三行
        Row row3 = sheet.createRow((short) 2);
        sheet.addMergedRegion(new CellRangeAddress(  2,2, 0, 6));// 设置单元格合并
        row3.createCell(0).setCellValue("归属于（"+repaymentschemodify_entity.getRefno()+")借款合同");
        row3.getCell(0).setCellStyle(styleOne);//设置样式1
        //创建单元格在第四行
        CellRangeAddress cra_row4 = new CellRangeAddress(  3,3, 0, 6);// 设置单元格合并
        Row row4 = sheet.createRow((short) 3);
        sheet.addMergedRegion(cra_row4);
        row4.createCell(0).setCellValue("还款计划表");
        for (int i = 0;i <= 6;i++) {
            Cell cell_temp = row4.getCell(i);
            if (cell_temp == null){
                cell_temp = row4.createCell(i);
            }
            cell_temp.setCellStyle(styleTwo);
        }
        //创建单元格在第五行
        Row row5 = sheet.createRow((short) 4);
        sheet.addMergedRegion(new CellRangeAddress(  4,4, 0, 2));// 设置单元格合并
        row5.createCell(0).setCellValue("贷款总额："+repaymentschemodify_entity.getLnamt());
        for (int i = 0;i <= 2;i++) {
            Cell cell_temp = row5.getCell(i);
            if (cell_temp == null){
                cell_temp = row5.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }
        //创建单元格在第六行
        Row row6 = sheet.createRow((short) 5);
        sheet.addMergedRegion(new CellRangeAddress(5,5,0,1));//合并第六行单元格第1和2两格
        row6.createCell(0).setCellValue("货币：人民币");
        for (int i = 0;i <= 1;i++) {
            Cell cell_temp = row6.getCell(i);
            if (cell_temp == null){
                cell_temp = row6.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }
        //创建单元格在第七行
        Row row7 = sheet.createRow((short) 6);
        sheet.addMergedRegion(new CellRangeAddress(6,6,0,1));//合并第七行单元格第1和2两格
        row7.createCell(0).setCellValue("贷款拨付/发放日期");
        for (int i = 0;i <= 1;) {
            Cell cell_temp = row7.getCell(i);
            if (cell_temp == null){
                cell_temp = row7.createCell(i);
            }
            cell_temp.setCellStyle(styleFour);
            i++;
        }

        row7.createCell(2).setCellValue("贷款总额");
        row7.getCell(2).setCellStyle(styleFour);

        row7.createCell(4).setCellValue("年利率（%）");
        sheet.addMergedRegion(new CellRangeAddress(6,6,4,5));//合并第七行单元格第5和6两格
        for (int i = 4;i <= 5;i++) {
            Cell cell_temp = row7.getCell(i);
            if (cell_temp == null){
                cell_temp = row7.createCell(i);
            }
            cell_temp.setCellStyle(styleFour);
        }

        row7.createCell(6).setCellValue("起息日期");
        row7.getCell(6).setCellStyle(styleFour);

        //创建单元格在第八行
        Row row8 = sheet.createRow((short) 7);
        sheet.addMergedRegion(new CellRangeAddress(  7,7, 0, 1));// 设置单元格合并
        row8.createCell(0).setCellValue(repaymentschemodify_entity.getDwndate());
        for (int i = 0;i <= 1;i++) {
            Cell cell_temp = row8.getCell(i);
            if (cell_temp == null){
                cell_temp = row8.createCell(i);
            }
            cell_temp.setCellStyle(styleFour);
        }

        row8.createCell(2).setCellValue(String.valueOf(repaymentschemodify_entity.getLnamt()));
        row8.getCell(2).setCellStyle(styleFour);

        row8.createCell(4).setCellValue(repaymentschemodify_dao.queryForIntrate(repaymentschemodify_entity.getRefno()));
        sheet.addMergedRegion(new CellRangeAddress(7,7,4,5));//合并第八行单元格第5和6两格
        for (int i = 4;i <= 5;i++) {
            Cell cell_temp = row8.getCell(i);
            if (cell_temp == null){
                cell_temp = row8.createCell(i);
            }
            cell_temp.setCellStyle(styleFour);
        }

        row8.createCell(6).setCellValue(repaymentschemodify_entity.getDwndate());
        row8.getCell(6).setCellStyle(styleFour);

        Row row10 = sheet.createRow((short) 9);
        row10.createCell(0).setCellValue("期数");
        row10.createCell(1).setCellValue("日期");
        row10.createCell(2).setCellValue("计息天数");
        row10.createCell(3).setCellValue("还款金额");
        row10.createCell(4).setCellValue("本金");
        row10.createCell(5).setCellValue("利息");
        row10.createCell(6).setCellValue("余额");
        row10.getCell(0).setCellStyle(styleFive);
        row10.getCell(1).setCellStyle(styleFive);
        row10.getCell(2).setCellStyle(styleFive);
        row10.getCell(3).setCellStyle(styleFive);
        row10.getCell(4).setCellStyle(styleFive);
        row10.getCell(5).setCellStyle(styleFive);
        row10.getCell(6).setCellStyle(styleFive);

        for(int i=0;i<repaymentScheduleList.size();i++){
            Row row10plus = sheet.createRow((short) (10 + i));
            row10plus.createCell(0).setCellValue(repaymentScheduleList.get(i).getInstno());
            row10plus.createCell(1).setCellValue(repaymentScheduleList.get(i).getStrdate());
            if(i==0){
                try {
                    row10plus.createCell(2).setCellValue((sdf.parse(repaymentScheduleList.get(i).getStrdate()).getTime() - sdf.parse(repaymentschemodify_entity.getDwndate()).getTime()) / 86400000);
                }
                catch (java.text.ParseException e){
                    e.printStackTrace();
                }
                }else{
                try {
                    row10plus.createCell(2).setCellValue((sdf.parse(repaymentScheduleList.get(i).getDuedate()).getTime()-sdf.parse(repaymentScheduleList.get(i).getStrdate()).getTime())/86400000);
                }
                catch (java.text.ParseException e){
                    e.printStackTrace();
                }
            }
            row10plus.createCell(3).setCellValue(repaymentScheduleList.get(i).getInstamt().toString());
            row10plus.createCell(4).setCellValue(repaymentScheduleList.get(i).getPrnamt().toString());
            row10plus.createCell(5).setCellValue(repaymentScheduleList.get(i).getIntamt().toString());
            row10plus.createCell(6).setCellValue(repaymentScheduleList.get(i).getLnosamt().toString());

            for (int j = 0; j < 7; j++) {
                row10plus.getCell(j).setCellStyle(styleSix);
            }
        }

        Row rowsum = sheet.createRow((short)(10+repaymentScheduleList.size()));
        rowsum.createCell(0).setCellValue("合计："+repaymentScheduleList.size()+"期");
        sheet.addMergedRegion(new CellRangeAddress(  10+repaymentScheduleList.size(),10+repaymentScheduleList.size(), 0, 2));// 设置单元格合并
        for (int i = 0;i <= 2;i++) {
            Cell cell_temp = rowsum.getCell(i);
            if (cell_temp == null){
                cell_temp = rowsum.createCell(i);
            }
            cell_temp.setCellStyle(styleSeven);
        }
        rowsum.createCell(3).setCellValue(String.valueOf(SumInfo.getSum_instamt()));
        rowsum.getCell(3).setCellStyle(styleEight);
        rowsum.createCell(4).setCellValue(String.valueOf(SumInfo.getSum_prnamt()));
        rowsum.getCell(4).setCellStyle(styleEight);
        rowsum.createCell(5).setCellValue(String.valueOf(SumInfo.getSum_intamt()));
        rowsum.getCell(5).setCellStyle(styleEight);
        rowsum.createCell(6).setCellValue("");
        rowsum.getCell(6).setCellStyle(styleEight);

        Row row26 = sheet.createRow((short) 25);
        sheet.addMergedRegion(new CellRangeAddress(25,25,0,1));//合并第26行单元格第1和2两格
        row26.createCell(0).setCellValue("借款人（签章）：");
        for (int i = 0;i <= 1;i++) {
            Cell cell_temp = row26.getCell(i);
            if (cell_temp == null){
                cell_temp = row26.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }

        sheet.addMergedRegion(new CellRangeAddress(25,25,4,6));//合并第26行单元格第5、6、7三格
        row26.createCell(4).setCellValue("贷款人（公章)：");
        for (int i = 4;i <= 6;i++) {
            Cell cell_temp = row26.getCell(i);
            if (cell_temp == null){
                cell_temp = row26.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }

        Row row27 = sheet.createRow((short) 26);
        sheet.addMergedRegion(new CellRangeAddress(26,26,0,2));//合并第27行单元格第1、2、3三格
        row27.createCell(0).setCellValue("法定代表人/授权代表（签字 ）：");
        for (int i = 0;i <= 2;i++) {
            Cell cell_temp = row27.getCell(i);
            if (cell_temp == null){
                cell_temp = row27.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }

        sheet.addMergedRegion(new CellRangeAddress(26,26,4,5));//合并第27行单元格第5和6两格
        row27.createCell(4).setCellValue("法定代表人/授权代表(签字）：");
        for (int i = 4;i <= 5;i++) {
            Cell cell_temp = row27.getCell(i);
            if (cell_temp == null){
                cell_temp = row27.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }

        Row row28 = sheet.createRow((short) 27);
        row28.createCell(0).setCellValue("日期：");
        row28.createCell(4).setCellValue("日期：");
        row28.getCell(0).setCellStyle(styleThree);
        row28.getCell(4).setCellStyle(styleThree);

        Row row30 = sheet.createRow((short) 29);
        sheet.addMergedRegion(new CellRangeAddress(29,29,0,2));//合并第30行单元格第1、2、3三格
        row30.createCell(0).setCellValue("共同借款人（公章）：");
        for (int i = 0;i <= 2;i++) {
            Cell cell_temp = row30.getCell(i);
            if (cell_temp == null){
                cell_temp = row30.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }

        Row row31 = sheet.createRow((short) 30);
        sheet.addMergedRegion(new CellRangeAddress(30,30,0,2));//合并第31行单元格第1、2、3三格
        row31.createCell(0).setCellValue("法定代表人/授权代表（签字 ）：");
        for (int i = 0;i <= 2;i++) {
            Cell cell_temp = row31.getCell(i);
            if (cell_temp == null){
                cell_temp = row31.createCell(i);
            }
            cell_temp.setCellStyle(styleThree);
        }

        Row row32 = sheet.createRow((short) 31);
        row32.createCell(0).setCellValue("日期：");
        row32.getCell(0).setCellStyle(styleThree);

        //文件保存在桌面
        try {
            FileOutputStream fileOut = new FileOutputStream("C:\\Users\\ecs\\Desktop\\还款计划表.xls");
            // 把上面创建的工作簿输出到文件中
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            return "文件已下载到桌面";
        }catch(IOException e){
            e.printStackTrace();
            return "文件下载失败";
        }
    }

}
